﻿Public Class perUsuario
    Inherits PersistenciaPadrao.AcessoBD

#Region "Transação"

    Public Sub configurarAberturaConexao()

        If perConexao.SqlComando Is Nothing OrElse perConexao.SqlConexao Is Nothing Then
            MyBase.abrirConexao()
            perConexao.SqlComando = MyBase.retornarComandoSqlServer
            perConexao.SqlConexao = MyBase.retornarConexaoSqlServer
        Else
            MyBase.configurarTransacaoSqlServer(perConexao.SqlComando, perConexao.SqlConexao)
        End If
    End Sub

    Public Sub configurarFechamentoConexao()
        MyBase.fecharConexao()
        perConexao.SqlComando = Nothing
        perConexao.SqlConexao = Nothing
    End Sub

#End Region

#Region "Métodos"

    Public Function inserirUsuario(ByVal sNome As String,
                                   ByVal sLogin As String,
                                   ByVal sSenha As String,
                                   ByVal bBloqueado As Boolean,
                                   ByVal bAbrirFecharConexao As Boolean) As Integer

        Dim sSql As String = String.Empty
        Dim objCriptografia As New UtilPadrao.Criptografia

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " INSERT INTO " &
               "    seg_usuarios " &
               " ( " &
               "    nome, " &
               "    login, " &
               "    senha, " &
               "    bloqueado" &
               " ) " &
               " VALUES " &
               " ( " &
               "    @nome, " &
               "    @login, " &
               "    @senha, " &
               "    @bloqueado " &
               " ); SELECT SCOPE_IDENTITY() "

        MyBase.limparParametros()
        MyBase.inserirParametro("@nome", sNome.Trim)
        MyBase.inserirParametro("@login", sLogin.Trim)
        MyBase.inserirParametro("@senha", objCriptografia.criptografar(sSenha.Trim))
        MyBase.inserirParametro("@bloqueado", bBloqueado)

        Return MyBase.executarConsultaCampo(sSql, bAbrirFecharConexao)

    End Function

    Public Sub editarUsuario(ByVal iCodUsuario As Integer,
                             ByVal sNome As String,
                             ByVal sLogin As String,
                             ByVal sSenha As String,
                             ByVal bBloqueado As Boolean,
                             ByVal bAbrirFecharConexao As Boolean)

        Dim sSql As String = String.Empty
        Dim objCriptografia As New UtilPadrao.Criptografia

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " update " &
               "   seg_usuarios " &
               " set " &
               "   nome = @nome, " &
               "   login = @login, " &
               "   senha = @senha, " &
               "   bloqueado = @bloqueado " &
               " where " &
               "   codUsuario = @codUsuario "

        MyBase.limparParametros()
        MyBase.inserirParametro("@nome", sNome)
        MyBase.inserirParametro("@login", sLogin.Trim)
        MyBase.inserirParametro("@senha", objCriptografia.criptografar(sSenha.Trim))
        MyBase.inserirParametro("@bloqueado", bBloqueado)
        MyBase.inserirParametro("@codUsuario", iCodUsuario)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Sub excluirUsuario(ByVal iCodUsuario As Integer,
                              ByVal bAbrirFecharConexao As Boolean)

        Dim sSql As String = String.Empty

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " delete from " &
               "   seg_usuarios " &
               " where " &
               "   codUsuario = @codUsuario "


        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodUsuario)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Sub inserirUsuarioGrupo(ByVal iCodUsuario As Integer,
                                   ByVal iCodGrupo As Integer,
                                   ByVal iCodEmpresa As Integer,
                                   ByVal bAbrirFecharConexao As Boolean)

        Dim sSql As String = String.Empty

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " insert into " &
               "    seg_usua_grupos " &
               " ( " &
               "    codUsuario, " &
               "    codGrupo, " &
               "    codEmpresa " &
               " ) " &
               " values " &
               " ( " &
               "    @codUsuario, " &
               "    @codGrupo, " &
               "    @codEmpresa " &
               " ) "


        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodUsuario)
        MyBase.inserirParametro("@codGrupo", iCodGrupo)
        MyBase.inserirParametro("@codEmpresa", iCodEmpresa)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Sub inserirUsuarioEmpresa(ByVal iCodUsuario As Int32,
                                     ByVal iCodEmpresa As Int32,
                                     ByVal bAbrirFecharConexao As Boolean)

        Dim sSql As String = String.Empty
        Dim iCodigo As Int32 = 0

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " INSERT INTO  " & Environment.NewLine &
               "    seg_usua_empresas " & Environment.NewLine &
               " ( " & Environment.NewLine &
               "    codUsuario,  " & Environment.NewLine &
               "    codEmpresa  " & Environment.NewLine &
               " ) " & Environment.NewLine &
               " VALUES " & Environment.NewLine &
               " ( " & Environment.NewLine &
               "    @codUsuario,  " & Environment.NewLine &
               "    @codEmpresa  " & Environment.NewLine &
               " ) "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodUsuario)
        MyBase.inserirParametro("@codEmpresa", iCodEmpresa)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Sub excluirUsuarioEmpresa(ByVal iCodUsuario As Int32,
                                     ByVal bAbrirFecharConexao As Boolean)

        Dim sSql As String = String.Empty
        Dim iCodigo As Int32 = 0

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " DELETE  " & Environment.NewLine &
               "    seg_usua_empresas " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "    codUsuario = @codUsuario "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodUsuario)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Sub excluirUsuarioGrupo(ByVal iCodUsuario As Integer,
                                   ByVal bAbrirFecharConexao As Boolean)

        Dim sSql As String = String.Empty

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " DELETE FROM " &
               "    seg_usua_grupos " &
               " WHERE " &
               "    codUsuario = @codUsuario "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodUsuario)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Function selecionarUsuarioCodigo(ByVal iCodigoUsu As Integer) As DataRow

        Dim sSql As String = String.Empty
        Dim datUsuario As New DataTable

        sSql = " SELECT " &
               "    seg_usuarios.* " &
               " FROM " &
               "    seg_usuarios " &
               " WHERE " &
               "    codUsuario = @codUsuario "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodigoUsu)

        datUsuario = MyBase.executarConsulta(sSql, True)
        If datUsuario.Rows.Count = 0 Then
            Return Nothing
        Else
            Return datUsuario.Rows(0)
        End If

    End Function

    Public Function selecionarModulosGrupos() As DataTable

        Dim sSql As String = String.Empty

        sSql = " SELECT " & Environment.NewLine &
               "    seg_modulos.codModulo, " & Environment.NewLine &
               "    seg_modulos.descricao, " & Environment.NewLine &
               "    0 permitido, " & Environment.NewLine &
               "    0 codGrupo, " & Environment.NewLine &
               "    '' nome, " & Environment.NewLine &
               "    0 codEmpresa " & Environment.NewLine &
               " FROM " & Environment.NewLine &
               "    seg_modulos " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "     seg_modulos.codModulo <> 1 " &
               " ORDER BY  " & Environment.NewLine &
               "    seg_modulos.codModulo "

        MyBase.limparParametros()

        Return MyBase.executarConsulta(sSql, True)

    End Function

    Public Function selecionarUsuariosGrupos(ByVal iCodUsuario As Int32) As DataTable

        Dim sSql As String = String.Empty

        sSql = " SELECT " & Environment.NewLine &
               "    seg_modulos.codModulo, " & Environment.NewLine &
               "    seg_modulos.descricao, " & Environment.NewLine &
               "    1 permitido, " & Environment.NewLine &
               "    seg_grupos_acesso.codGrupo, " & Environment.NewLine &
               "    seg_grupos_acesso.nome, " & Environment.NewLine &
               "    seg_usua_grupos.codEmpresa " & Environment.NewLine &
               " FROM " & Environment.NewLine &
               "    seg_usua_grupos " & Environment.NewLine &
               " INNER JOIN seg_grupos_acesso ON seg_usua_grupos.codGrupo = seg_grupos_acesso.codGrupo " & Environment.NewLine &
               " INNER JOIN seg_modulos ON seg_grupos_acesso.codModulo = seg_modulos.codModulo " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "    seg_usua_grupos.codUsuario = @codUsuario " & Environment.NewLine &
               " ORDER BY  " & Environment.NewLine &
               "    seg_modulos.codModulo "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodUsuario)

        Return MyBase.executarConsulta(sSql, True)

    End Function

    Public Function selecionarUsuarioEmpresas(ByVal iCodUsuario As Int32) As DataTable

        Dim sSql As String = String.Empty

        sSql = " SELECT " & Environment.NewLine &
               "    seg_empresa.codEmpresa, " & Environment.NewLine &
               "    seg_empresa.razaoSocial, " & Environment.NewLine &
               "    0 permitido " & Environment.NewLine &
               " FROM " & Environment.NewLine &
               "    seg_empresa " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "    seg_empresa.codEmpresa NOT IN (SELECT seg_usua_empresas.codEmpresa FROM seg_usua_empresas WHERE seg_usua_empresas.codUsuario = @codUsuario) " & Environment.NewLine &
               " UNION   " & Environment.NewLine &
               " SELECT " & Environment.NewLine &
               "    seg_empresa.codEmpresa, " & Environment.NewLine &
               "    seg_empresa.razaoSocial, " & Environment.NewLine &
               "    1 permitido " & Environment.NewLine &
               " FROM " & Environment.NewLine &
               "    seg_empresa " & Environment.NewLine &
               " INNER JOIN seg_usua_empresas ON seg_empresa.codEmpresa = seg_usua_empresas.codEmpresa " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "    seg_usua_empresas.codUsuario = @codUsuario " &
               " ORDER BY  " & Environment.NewLine &
               "    seg_empresa.codEmpresa "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codUsuario", iCodUsuario)

        Return MyBase.executarConsulta(sSql, True)

    End Function

    Public Function selecionarUsuarios() As DataTable

        Dim sSql As String = String.Empty
        Dim datUsuario As New DataTable

        sSql = " SELECT " &
               "    codUsuario, " &
               "    nome, " &
               "    login " &
               " FROM " &
               "    seg_usuarios "

        MyBase.limparParametros()

        Return MyBase.executarConsulta(sSql, True)

    End Function

    Public Function seUsuarioExistente(ByVal sLogin As String,
                                       ByVal iCodUsu As Int32) As Boolean

        Dim sSql As String = String.Empty
        Dim datUsuario As New DataTable
        Dim objSeguranca As New UtilPadrao.Criptografia

        sSql = " SELECT " &
               "     seg_usuarios.* " &
               " FROM " &
               "     seg_usuarios " &
               " WHERE " &
               "     login = @login " &
               " AND codUsuario <> @codUsuario "

        MyBase.limparParametros()
        MyBase.inserirParametro("@login", sLogin.Trim)
        MyBase.inserirParametro("@codUsuario", iCodUsu)

        datUsuario = MyBase.executarConsulta(sSql, True)
        If datUsuario.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Function selecionarUsuario(ByVal sLogin As String,
                                      ByVal sSenha As String) As DataRow

        Dim sSql As String = String.Empty
        Dim datUsuario As New DataTable
        Dim objSeguranca As New UtilPadrao.Criptografia

        sSql = " SELECT DISTINCT " &
               "  seg_usuarios.*, " &
               "  seg_usua_grupos.CODGRUPO " &
               " FROM " &
               "  seg_usuarios " &
               " LEFT JOIN seg_usua_grupos ON seg_usuarios.codUsuario = seg_usua_grupos.codUsuario " &
               " LEFT JOIN seg_grupos_itens ON seg_usua_grupos.codGrupo = seg_grupos_itens.codGrupo " &
               " LEFT JOIN seg_modulo_menus ON seg_grupos_itens.codMenu = seg_modulo_menus.codMenu " &
               " WHERE " &
               "  login = @login " &
               " AND senha = @senha  " &
               " AND (seg_modulo_menus.codModulo = 2) "

        MyBase.limparParametros()
        MyBase.inserirParametro("@login", sLogin.Trim)
        MyBase.inserirParametro("@senha", objSeguranca.criptografar(sSenha.Trim))

        datUsuario = MyBase.executarConsulta(sSql, True)
        If datUsuario.Rows.Count > 0 Then
            Return datUsuario.Rows(0)
        Else
            Return Nothing
        End If

    End Function

#End Region

End Class
